/****** Object:  Stored Procedure dbo.asi_UpdateCampaignResponses    Script Date: 11/15/2005 3:00 PM ******/
CREATE    PROCEDURE [dbo].[asi_CsUpdateCampaignResponses]

@useImplicit bit,
@userKey uniqueidentifier
AS
--The following values were used during testing
--set @useImplicit = 1
--set @userKey = '00000000-0000-0000-0000-000000000001'
-- updated 15 Feb 2007 RRK to add some Seco stuff; ServiceCentral in particular.
-- updated 16 Feb 2007 RRK DT19614 -- fix problem with auto-mapped Dues
declare @firstTransNumber int
declare @lastTransNumber int
declare @firstOrderNumber float
declare @lastOrderNumber float
declare @now datetime
declare @debug bit
set @debug = 1
--Get the last TRANS row used in a response update and update the number to the current max
if @debug = 1
    print 'Get the last TRANS row used in a response update and update the number to the current max'
execute asi_GetTransResponseStart @userKey, @firstTransNumber output, @lastTransNumber output
if @debug = 1
    print 'First Trans Number:' + convert(varchar(10),@firstTransNumber) +'; Last Tans Number ' + convert(varchar(10),@lastTransNumber)
--Get the last ORDERS row used in a response update and update the number to the current max
if @debug = 1
    print 'Get the last ORDERS row used in a response update and update the number to the current max'
execute asi_GetOrderResponseStart @userKey, @firstOrderNumber output, @lastOrderNumber output
if @debug = 1
    print 'First Order: ' + convert(varchar(10),@firstOrderNumber) +'; Last Order:' + convert(varchar(10),@lastOrderNumber)

-- HACK-- this must be removed to prevent duplicate responses
--select @firstOrderNumber = 0,@lastOrderNumber = 10000,@firstTransNumber=0,@lastTransNumber =100000
-- END HACK

set @now = getdate()
if exists (select * from tempdb..sysobjects where id = OBJECT_ID(N'tempdb..#tmpResponse'))
   drop table #tmpResponse
create table #tmpResponse(
SourceTable char(1),
TransNumber int,
LineSub decimal(18,4),
TransDate datetime,
Id varchar(10),
UserKey uniqueidentifier,
ProductCode varchar(31),
SourceCode varchar(60),
TotalRevenue decimal(18,4),
WasSolicited tinyint not null default 1,
InvoiceReferenceNum int default -1)

-- Dues Trans records with matching source code
-- bp 6/6/05 - We want to recognize separate responses for separate source codes, even
--     if trans number is the same, so add t.SOURCE_CODE to 'group by' clause
if @debug = 1
    print 'Due Trans records with matching source code'
insert #tmpResponse(SourceTable, TransNumber, SourceCode, Id, TransDate)
select  'D', t.TRANS_NUMBER, min(t.SOURCE_CODE), max(t.BT_ID), min(t.TRANSACTION_DATE)
from Trans t
  inner join SourceCode sg on t.SOURCE_CODE = sg.Code
where t.TRANS_NUMBER > @firstTransNumber and t.TRANS_NUMBER <= @lastTransNumber
   and t.TRANSACTION_TYPE = 'DIST'
   and t.SOURCE_SYSTEM in ('DUES','SC')
   and t.JOURNAL_TYPE = 'PAY'
   and t.SOURCE_CODE > ' ' and t.SOURCE_CODE <> 'WEB'
   and sg.SourceCodeStatusCode = 0 -- 0 is Active
group by t.TRANS_NUMBER, t.SOURCE_CODE
-- FR Trans records with matching source code
if @debug = 1
    print 'FR Trans records with matching source code'
insert #tmpResponse(SourceTable, TransNumber, SourceCode, Id, TransDate)
select  'T', t.TRANS_NUMBER, min(t.SOURCE_CODE), max(t.BT_ID), min(t.TRANSACTION_DATE)
from Trans t
    inner join SourceCode sg on t.SOURCE_CODE = sg.Code
where t.TRANS_NUMBER > @firstTransNumber and t.TRANS_NUMBER <= @lastTransNumber
   and t.TRANSACTION_TYPE = 'DIST'
   and t.SOURCE_SYSTEM = 'FR'
   and t.JOURNAL_TYPE = 'IN'
   and t.SOURCE_CODE > ' ' and t.SOURCE_CODE <> 'WEB'
   and sg.SourceCodeStatusCode = 0 -- 0 is Active
group by t.TRANS_NUMBER
if @debug = 1
    print'Meeting Trans records with matching source code'
-- Meeting Trans records with matching source code
-- DT16091 - Include INVOICE_REFERENCE_NUM in #tmpResponse. We'll need this to filter out
-- past responses to the same event, and to filter out duplicate responses to the same event.
insert #tmpResponse(SourceTable, TransNumber, SourceCode, Id, TransDate, InvoiceReferenceNum)
select  'T', t.TRANS_NUMBER, t.SOURCE_CODE, max(t.BT_ID), min(t.TRANSACTION_DATE), t.INVOICE_REFERENCE_NUM
from Trans t
inner join SourceCode sg on t.SOURCE_CODE = sg.Code
where t.TRANS_NUMBER > @firstTransNumber and t.TRANS_NUMBER <= @lastTransNumber
   and t.TRANSACTION_TYPE = 'DIST'
   and t.SOURCE_SYSTEM = 'MEETING'
   and t.JOURNAL_TYPE = 'IN'
   and t.SOURCE_CODE > ' ' and t.SOURCE_CODE <> 'WEB'
   and sg.SourceCodeStatusCode = 0 -- 0 is Active
group by t.TRANS_NUMBER, t.SOURCE_CODE, t.INVOICE_REFERENCE_NUM
if @debug = 1
    print 'Orders and Expo transactions with matching source code'
--Orders and Expo transactions with matching source code
insert #tmpResponse(SourceTable, TransNumber, SourceCode, Id, TransDate, TotalRevenue)
select  'O', o.ORDER_NUMBER, o.SOURCE_CODE, o.BT_ID, o.ORDER_DATE, o.LINE_TOTAL
from Orders o
  inner join SourceCode sg on o.SOURCE_CODE = sg.Code
where ORDER_NUMBER > @firstOrderNumber and ORDER_NUMBER <= @lastOrderNumber
   and o.SOURCE_CODE > ' ' and o.SOURCE_CODE <> 'WEB'
   and sg.SourceCodeStatusCode = 0 -- 0 is Active
   -- bp 6/6/05 - Meetings tranactions will be handled separately
   and o.SOURCE_SYSTEM <> 'MEETING'
   -- Don't include premium donation orders
   and not exists(select 1 from Donation_Premium where ORDER_NUMBER = o.ORDER_NUMBER)
   and (o.SOURCE_CODE IN (SELECT Code from SourceCode where SourceCodeStatusCode=0))    
if @useImplicit = 1
begin
if @debug = 1
    print 'Dues Trans records with matching product and no explicit source code'
--Dues Trans records with matching product and no explicit source code
insert #tmpResponse(SourceTable, TransNumber, LineSub, ProductCode, Id, TransDate)
select distinct 'D', t.TRANS_NUMBER, (t.LINE_NUMBER + t.SUB_LINE_NUMBER / 1000), t.PRODUCT_CODE, t.BT_ID, t.TRANSACTION_DATE
from Trans t
  inner join AppealProduct ap on t.PRODUCT_CODE = ap.ProductCode
  inner join AppealMain am on ap.AppealKey = am.AppealKey
where t.TRANS_NUMBER > @firstTransNumber and t.TRANS_NUMBER <= @lastTransNumber
   and t.TRANSACTION_TYPE = 'DIST'
   and t.SOURCE_SYSTEM in ('DUES','SC')
   and t.JOURNAL_TYPE = 'PAY'
   and (t.SOURCE_CODE = '' or t.SOURCE_CODE = 'WEB')
   and am.AppealStatusCode = 0 -- 0 is Active
   and not exists(select 1 from #tmpResponse where TransNumber = t.TRANS_NUMBER and LineSub is null and SourceTable = 'T')
--Keep only the lowest Line/SubLine for each transaction number
delete tr
from #tmpResponse tr
where (SourceTable = 'T' or SourceTable = 'D')
   and LineSub >  (select min(LineSub)
      from #tmpResponse
      where TransNumber = tr.TransNumber
        and (SourceTable = 'T' or SourceTable = 'D'))
--FR Trans records with matching product and no explicit source code
if @debug = 1
    print 'FR Trans records with matching product and no explicit source code'
insert #tmpResponse(SourceTable, TransNumber, LineSub, ProductCode, Id, TransDate)
select distinct 'T', t.TRANS_NUMBER, (t.LINE_NUMBER + t.SUB_LINE_NUMBER / 1000), t.PRODUCT_CODE, t.BT_ID, t.TRANSACTION_DATE
from Trans t
  inner join AppealProduct ap on t.PRODUCT_CODE = ap.ProductCode
  inner join AppealMain am on ap.AppealKey = am.AppealKey
where t.TRANS_NUMBER > @firstTransNumber and t.TRANS_NUMBER <= @lastTransNumber
   and t.TRANSACTION_TYPE = 'DIST'
   and t.SOURCE_SYSTEM = 'FR'
   and t.JOURNAL_TYPE = 'IN'
   and (t.SOURCE_CODE = '' or t.SOURCE_CODE = 'WEB')
   and am.AppealStatusCode = 0 -- 0 is Active
   and not exists(select 1 from #tmpResponse where TransNumber = t.TRANS_NUMBER and LineSub is null and SourceTable = 'T')
--Keep only the lowest Line/SubLine for each transaction number
delete tr
from #tmpResponse tr
where SourceTable = 'T'
   and LineSub >  (select min(LineSub)
      from #tmpResponse
      where TransNumber = tr.TransNumber
        and SourceTable = 'T')

--Meeting Trans records with matching product and no explicit source code
if @debug = 1
    print 'Meeting Trans records with matching product and no explicit source code'
insert #tmpResponse(SourceTable, TransNumber, LineSub, ProductCode, Id, TransDate, InvoiceReferenceNum)
select distinct 'T', t.TRANS_NUMBER, (t.LINE_NUMBER + t.SUB_LINE_NUMBER / 1000), t.PRODUCT_CODE, t.BT_ID, t.TRANSACTION_DATE,
    t.INVOICE_REFERENCE_NUM
from Trans t
  inner join AppealProduct ap on t.PRODUCT_CODE = ap.ProductCode
  inner join AppealMain am on ap.AppealKey = am.AppealKey
where t.TRANS_NUMBER > @firstTransNumber and t.TRANS_NUMBER <= @lastTransNumber
   and t.TRANSACTION_TYPE = 'DIST'
   and t.SOURCE_SYSTEM = 'MEETING'
   and t.JOURNAL_TYPE = 'IN'
   and (t.SOURCE_CODE = '' or t.SOURCE_CODE = 'WEB')
   and am.AppealStatusCode = 0 -- 0 is Active
   and not exists(select 1 from #tmpResponse where TransNumber = t.TRANS_NUMBER and LineSub is null and SourceTable = 'T')
--Keep only the lowest Line/SubLine for each transaction number
delete tr
from #tmpResponse tr
where SourceTable = 'T'
   and LineSub >  (select min(LineSub)
      from #tmpResponse
      where TransNumber = tr.TransNumber
        and SourceTable = 'T')
if @debug = 1
    print 'Order and Expo transactions with matching products and no explicit source code'
--Order and Expo transactions with matching products and no explicit source code
insert #tmpResponse(SourceTable, TransNumber, LineSub, ProductCode, Id, TransDate, TotalRevenue)
select distinct 'O', ol.ORDER_NUMBER, ol.LINE_NUMBER, ol.PRODUCT_CODE, o.BT_ID, o.ORDER_DATE, o.LINE_TOTAL
from Orders o
  inner join Order_Lines ol on o.ORDER_NUMBER = ol.ORDER_NUMBER
  inner join AppealProduct ap on ol.PRODUCT_CODE = ap.ProductCode
  inner join AppealMain am on ap.AppealKey = am.AppealKey
where o.ORDER_NUMBER > @firstOrderNumber and o.ORDER_NUMBER <= @lastOrderNumber
   and (o.SOURCE_CODE = '' or o.SOURCE_CODE = 'WEB')
   and am.AppealStatusCode = 0 -- 0 is Active
   -- bp 6/6/05 - Meetings tranactions will be handled separately
   and o.SOURCE_SYSTEM <> 'MEETING'
   and not exists(select 1 from #tmpResponse where TransNumber = o.ORDER_NUMBER and LineSub is null and SourceTable = 'O')
   -- Don't include premium donation orders
   and not exists(select 1 from Donation_Premium where ORDER_NUMBER = o.ORDER_NUMBER)
--Keep only the lowest Line for each order number
delete tr
from #tmpResponse tr
where SourceTable = 'O'
   and LineSub >  (select min(LineSub)
      from #tmpResponse
      where TransNumber = tr.TransNumber
        and SourceTable = 'O')
end
if @debug = 1
    print 'Set the UserKey value'
--Set the UserKey value
-- EO 09-20-06 Unified Login changed this from UserMain to ContactMain
update tr
set UserKey = cm.ContactKey
from #tmpResponse tr
  inner join ContactMain cm on tr.Id = cm.SyncContactID

-- Treat 'WEB' SourceCode as we would if no SourceCode was entered
update #tmpResponse
set SourceCode = null
where SourceCode = 'WEB'

if @debug = 1
    print 'Attempt to find a match using the product and user'
--Attempt to find a match using the product and user
update tr
set SourceCode =   (select top 1 sc.Code
       from AppealProduct ap
        inner join vBoSolicitation s on ap.AppealKey = s.AppealKey
        inner join vBoSourceCode sc on s.SolicitationKey = sc.SolicitationKey
       where ap.ProductCode = tr.ProductCode
         and exists( select 1
           from ListItem
           where ListKey = sc.SourceCodeKey
             and ObjectKey = tr.UserKey)
     and sc.LastDropDate = (select max(sc2.LastDropDate)
        from     AppealProduct ap2
        inner join vBoSolicitation s2 on ap2.AppealKey = s2.AppealKey
        inner join vBoSourceCode sc2 on s2.SolicitationKey = sc2.SolicitationKey
        where     ap2.ProductCode = tr.ProductCode))
from #tmpResponse tr
where tr.SourceCode is null

if @debug = 1
    print 'Attempt to find a match just using the product'
--Attempt to find a match just using the product
-- use  the SourceCode with the Latest Drop Doate
update    tr
set     SourceCode =   (select     top 1 sc.Code
     from     AppealProduct ap
      inner join vBoSolicitation s on ap.AppealKey = s.AppealKey
      inner join vBoSourceCode sc on s.SolicitationKey = sc.SolicitationKey
    where     ap.ProductCode = tr.ProductCode
     -- E0 09-21-06
     -- I'm not sure how the following ever worked      
    /*and  sc.LastDropDate = (select max(sc2.LastDropDate)
        from     AppealProduct ap2
        inner join vBoSolicitation s2 on ap2.AppealKey = s2.AppealKey
        inner join vBoSourceCode sc2 on s2.SolicitationKey = sc2.SolicitationKey
        where     ap2.ProductCode = tr.ProductCode)*/
    -- changing it to this
    order by sc.LastDropDate
),
    WasSolicited = 0
from #tmpResponse tr
where tr.SourceCode is null
if @debug = 1
    print 'Remove records without a source code'
--Remove records without a source code
delete #tmpResponse
where SourceCode is null
print 'Calculate revenue on records that originated from Trans (non-Dues)'
--Calculate revenue on records that originated from Trans (non-Dues)
update #tmpResponse
set TotalRevenue = (select  sum(ISNULL(INVOICE_CHARGES, 0))
       from Trans
       -- DT16015 - If source code is explicitly associated with the Trans entry, make sure revenue
       --  is not attributed to another source code
       where TRANS_NUMBER = #tmpResponse.TransNumber and (SOURCE_CODE = #tmpResponse.SourceCode))
where SourceTable = 'T'
if @debug = 1
    print 'Calculate revenue on records that originated from Trans (Dues)'
--Calculate revenue on records that originated from Trans (Dues)
-- BP 6/9/05 - When calculating revenue, group Trans lines based on
--           TRANS_NUMBER and SOURCE_CODE
-- BP 9/29/05 - DT 14640 - When calculating revenue, rather than using ABS(), multiply by -1, so negative payments are treated as negative revenue.
update #tmpResponse
set TotalRevenue = (select  (sum(AMOUNT))*-1
       from Trans
       where TRANS_NUMBER = #tmpResponse.TransNumber and (SOURCE_CODE = #tmpResponse.SourceCode or SOURCE_CODE = '')
     and TRANSACTION_TYPE='DIST' and SOURCE_SYSTEM in ('DUES','SC') and JOURNAL_TYPE='PAY')
where SourceTable = 'D'

if exists (select * from tempdb..sysobjects where id = OBJECT_ID(N'tempdb..#bySourceCode'))
   drop table #bySourceCode
create table #bySourceCode(SourceCode nvarchar(60), TotalRevenue decimal(18,4), FirstResponse datetime, LastResponse datetime, LowResponse decimal(18,4), HighResponse decimal(18,4), TotalResponses int)

-- BP 9/30/05 Modify source code aggregation so negative revenue rows are handled properly

--Aggregate the responses by source code

-- When calculating TotalRevenue we want to include negative revenue responses
if @debug = 1
    print 'Aggregate the responses by source code'
insert #bySourceCode
select SourceCode,
    sum(ISNULL(TotalRevenue, 0)), -- TotalRevenue
    null, -- FirstResponse
    null, -- LastResponse
    null, -- LowResponse
    null, -- HighResponse
    0  -- TotalResponses
from #tmpResponse
group by SourceCode

-- BP 9/30/05 Remove negative revenue responses so that they aren't seen as responses
-- when calculating FirstResponse, LastResponse, LowResponse HighResponse, and TotalResponses
-- and creating/updating campaign and appeal participation records
if @debug = 1
    print 'Remove negative revenue responses so that they aren''t seen as responses'
delete #tmpResponse where TotalRevenue<0

update #bySourceCode
    set
    FirstResponse =
        (select min(TransDate)
            from #tmpResponse
            where #tmpResponse.SourceCode=#bySourceCode.SourceCode),
    LastResponse =
        (select max(TransDate)
            from #tmpResponse
            where #tmpResponse.SourceCode=#bySourceCode.SourceCode),
    LowResponse =
        (select min(TotalRevenue)
            from #tmpResponse
            where #tmpResponse.SourceCode=#bySourceCode.SourceCode),
    HighResponse =
        (select max(TotalRevenue)
            from #tmpResponse
            where #tmpResponse.SourceCode=#bySourceCode.SourceCode)
        from #bySourceCode, #tmpResponse

-- BP 11/14/05 DT 16091
-- When calculating TotalResponses we don't want to include past responses to
-- the same event by the same contact for the same SourceCode. Also, for multiple registrations to the same
-- event by the same contact for the same SourceCode, we want to count as one response.
--
-- Remove past responses
if @debug = 1
    print 'Remove past responses'
delete #tmpResponse where
    exists (
        select * from Trans where INVOICE_REFERENCE_NUM = #tmpResponse.InvoiceReferenceNum and
            SOURCE_CODE = #tmpResponse.SourceCode and
            TRANS_NUMBER <= @firstTransNumber)
-- Calculate TotalResponses. Entries with the same InvoiceReferenceNum should
-- be grouped together and only count as one response. Entries with
-- InvoiceReferenceNum = -1 are each counted as a response.
update #bySourceCode
    set
    TotalResponses =
        (select count(distinct InvoiceReferenceNum)
            from #tmpResponse
            where #tmpResponse.SourceCode=#bySourceCode.SourceCode and InvoiceReferenceNum<>-1)
         +
         (select count(*)
            from #tmpResponse
            where #tmpResponse.SourceCode=#bySourceCode.SourceCode and InvoiceReferenceNum=-1)
    from #bySourceCode, #tmpResponse

if exists (select * from tempdb..sysobjects where id = OBJECT_ID(N'tempdb..#byAppeal'))
   drop table #byAppeal
create table #byAppeal(CampaignKey uniqueidentifier, AppealKey uniqueidentifier, SolicitationKey uniqueidentifier, SolicitationGroupKey uniqueidentifier, UserKey uniqueidentifier, WasSolicited tinyint)
if @debug = 1
    print 'Aggregate the responses by appeal and user'
--Aggregate the responses by appeal and user
insert #byAppeal
select distinct c.CampaignKey, a.AppealKey, s.SolicitationKey, sc.SourceCodeKey,  tr.UserKey, tr.WasSolicited
from #tmpResponse tr
  inner join vBoSourceCode sc on tr.SourceCode = sc.Code
  inner join vBoSolicitation s on sc.SolicitationKey = s.SolicitationKey
  inner join vBoAppeal a on s.AppealKey = a.AppealKey
  inner join vBoCampaign c on a.CampaignKey = c.CampaignKey

--Allow only one record per Appeal and User combination
set rowcount 1

declare @appealKey uniqueidentifier
declare @campaignKey uniqueidentifier
declare @respondentUserKey uniqueidentifier

select @appealKey = AppealKey, @campaignKey = CampaignKey, @respondentUserKey = UserKey
from #byAppeal
group by CampaignKey, AppealKey, UserKey
having count(*) > 1
while @@ROWCOUNT > 0
begin
delete #byAppeal
  where AppealKey = @appealKey
    and CampaignKey = @campaignKey
    and UserKey = @respondentUserKey
select @appealKey = AppealKey, @campaignKey = CampaignKey, @respondentUserKey = UserKey
  from #byAppeal
  group by CampaignKey, AppealKey, UserKey
  having count(*) > 1
end

set rowcount 0

begin transaction

--Update SourceCode
if @debug = 1
    print 'Update SourceCOde'

update sg
set TotalRevenue = sg.TotalRevenue + bsc.TotalRevenue,
  FirstResponseDate =     case
                  when sg.FirstResponseDate is NULL then bsc.FirstResponse
                when bsc.FirstResponse is NULL then sg.FirstResponseDate
                  when bsc.FirstResponse < sg.FirstResponseDate then bsc.FirstResponse
                  else sg.FirstResponseDate end,
  LastResponseDate =     case
                  when sg.LastResponseDate is NULL then bsc.LastResponse
                when bsc.LastResponse is NULL then sg.LastResponseDate
                  when bsc.LastResponse > sg.LastResponseDate then bsc.LastResponse
                  else sg.LastResponseDate end,
  LowResponseAmount =     case
                  when sg.LowResponseAmount < 0 then bsc.LowResponse
                when bsc.LowResponse < 0 then sg.LowResponseAmount
                  when bsc.LowResponse < sg.LowResponseAmount then bsc.LowResponse                               
                else sg.LowResponseAmount end,
  HighResponseAmount = case
                when bsc.HighResponse > sg.HighResponseAmount then bsc.HighResponse
                else sg.HighResponseAmount end,
  TotalPositiveResponse = sg.TotalPositiveResponse + bsc.TotalResponses,
  ResponsesTotalledOn = @now,
  UpdatedOn = @now,
  UpdatedByUserKey = @userKey
from SourceCode sg
  inner join #bySourceCode bsc on sg.Code = bsc.SourceCode

-- DT 16091 - Don't attempt to create appeal or campaign participation entries if the User is unknown. This can happen when a new contact
-- is registered for an event and is not added to the db.
delete from #byAppeal where UserKey is null
--Create campaign opt-in
if @debug = 1
    print 'Create campaign opt-in'
insert CampaignParticipation(CampaignParticipationKey, CampaignKey, RespondentUserKey, ResponseTypeCode, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn)
select newid(), CampaignKey, UserKey, 1, @userKey, @now, @userKey, @now -- UnsolicitedResponse = Opt-In = ResponseStatus of one (1)
from #byAppeal ba
where not exists(select 1 from CampaignParticipation where CampaignKey = ba.CampaignKey and RespondentUserKey = ba.UserKey)

if @debug = 1
    print 'Update appeal participation'
--Update appeal participation
update ap
set SolicitationKey = ba.SolicitationKey,
  AppealKey = ba.AppealKey,
  ResponseTypeCode = case when WasSolicited = 1 then 0 else 1 end,
  UpdatedOn = @now,
  UpdatedByUserKey = @userKey
from AppealParticipation ap
  inner join #byAppeal ba on ap.AppealKey = ba.AppealKey and ap.RespondentUserKey = ba.UserKey
if @debug = 1
    print 'Create appeal participation'    
--Create appeal participation
insert AppealParticipation(AppealParticipationKey, AppealKey, RespondentUserKey, SolicitationKey, ResponseTypeCode, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn,SourceCodeKey)
select newid(), ba.AppealKey, ba.UserKey, ba.SolicitationKey, case when WasSolicited = 1 then 0 else 1 end, @userKey, @now, @userKey, @now,SolicitationGroupKey --SolicitedResponse = Responded = ResponseStatus of zero (0)
from #byAppeal ba
--INNER JOIN SourceCode SC ON SC.Code = ba.SourceCode
where not exists(select 1 from AppealParticipation where AppealKey = ba.AppealKey and RespondentUserKey = ba.UserKey)

commit transaction

if exists (select * from tempdb..sysobjects where id = OBJECT_ID(N'tempdb..#byAppeal'))
   drop table #byAppeal
if exists (select * from tempdb..sysobjects where id = OBJECT_ID(N'tempdb..#bySourceCode'))
   drop table #bySourceCode
if exists (select * from tempdb..sysobjects where id = OBJECT_ID(N'tempdb..#tmpResponse'))
   drop table #tmpResponse

GO